﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DBUtil.MetaData;
using System.Linq;
using System.Collections.Concurrent;
using System.Threading;
using MySqlConnector;
using DBUtil.Provider.MySql.MetaData;
using DotNetCommon;
using System.Data.Common;
using System.Threading.Tasks;
using DotNetCommon.Extensions;
using DBUtil.SqlSegment;
using DBUtil.Provider.MySql.SqlSegment;
using DotNetCommon.Data;
using System.Reflection;
using DotNetCommon.Logger;

namespace DBUtil.Provider.MySql
{
    /// <summary>
    /// MySql操作对象
    /// </summary>
    public partial class MySqlAccess : DBAccess
    {
        private static ILogger<MySqlAccess> logger = LoggerFactory.CreateLogger<MySqlAccess>();

        #region 表名或标识符分析/解析
        /// <summary>
        /// 标识符引用标记: [],""
        /// </summary>
        public override List<string> QuoteIdentifierCharacters
        {
            get => new List<string>() { "``", "\"\"" };
        }
        #endregion

        #region 创建DbCommand
        /// <summary>
        /// 创建IDbCommand
        /// </summary>
        /// <returns></returns>
        protected override DbCommand CreateCommand()
        {
            return new MySqlCommand();
        }
        #endregion

        #region 创建DataAdapter
        /// <summary>
        /// 创建IDataAdapter
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns></returns>
        protected override DataAdapter CreateAdapter(DbCommand cmd)
        {
            return new MySqlDataAdapter(cmd as MySqlCommand);
        }
        #endregion

        #region CreatePara 创建参数
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <returns></returns>
        public override DbParameter CreatePara()
        {
            return new MySqlParameter();
        }
        #endregion

        #region MySql参数

        private string _dataBaseUserName = "";
        /// <summary>
        /// 操作当前数据库的用户名(区别于LoginUserName,就像dbo区别于sa一样)
        /// </summary>
        public override string UserName
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_dataBaseUserName))
                {
                    lock (this)
                    {
                        if (string.IsNullOrWhiteSpace(_dataBaseUserName))
                        {
                            _dataBaseUserName = SelectScalar<string>("select user()");
                        }
                    }
                }
                return _dataBaseUserName;
            }
        }

        /// <summary>
        /// 登录数据库使用的用户名
        /// </summary>
        public override string LoginUserName => UserName;

        private string _dbname = string.Empty;
        /// <summary>
        /// 当前连接的数据库名称
        /// </summary>
        public override string DataBase => _dbname;

        private static ConcurrentDictionary<string, string> _versionCaches = new ConcurrentDictionary<string, string>();
        public override string DBVersion => _versionCaches.GetOrAdd(DBConn, _ => SelectScalar<string>("select version()"));

        /// <summary>
        /// 默认的架构/模式
        /// </summary>
        public override string DefaultSchema => DataBase;

        #endregion

        #region 初始化
        private static string appName = null;
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="DBConn">连接字符串</param>
        /// <param name="Settings">设置</param>
        public MySqlAccess(string DBConn, DBSetting Settings)
        {
            this.DBConn = DBConn;
            this.Settings = Settings;
            var builder = new MySqlConnectionStringBuilder();
            var defaultAppname = builder.ApplicationName;
            builder.ConnectionString = this.DBConn;
            if (defaultAppname == builder.ApplicationName)
            {
                if (appName == null)
                {
                    lock (typeof(MySqlAccess))
                    {
                        if (appName == null)
                        {
                            appName = $"{Assembly.GetEntryAssembly().GetName().Name}- DBUtil - {builder.ApplicationName}";
                            if (appName.Length > 128) appName = appName.Substring(0, 100);
                        }
                    }
                }
                builder.ApplicationName = appName;
            }

            builder.AllowUserVariables = true;
            this.Connection = new MySqlConnection(builder.ToString());
            //this.Connection = new SqlConnection(DBConn);
            this._dbname = this.Connection.Database;
            InitServerInfo();
        }

        private void InitServerInfo()
        {
            this.ParaPrefix = "@";
            this.DBType = Consts.ServerType;
        }
        #endregion        

        #region 将任意对象转为可拼接的sql
        /// <summary>
        /// 将任意对象转为可拼接的sql
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="args">参数,如: 
        /// <list type="number">
        /// <item>guid可使用 "N/D/B/P/X"</item>
        /// <item>datetime可使用 "yyyy-MM-dd HH:mm:ss.fff"</item>
        /// </list>
        /// </param>
        /// <returns></returns>
        public override Result<string> ConvertToSqlSegment(object obj)
        {
            if (obj is DateTimeOffset dateTimeOffset) return Result.Ok($"'{dateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.fffzzz")}'");
            var res = base.ConvertToSqlSegment(obj);
            if (res.Success) return res;

            //TODO 
            return Result.NotOk($"未能转换 {obj.GetType().FullName} 类型数据到sql!");
        }
        #endregion

        #region GetSqlForPageSize
        /// <summary>获得分页的查询语句
        /// </summary>
        /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
        /// <param name="strOrder">排序字句如:order by id</param>
        /// <param name="PageSize">页面大小,如:10</param>
        /// <param name="PageIndex">页面索引从1开始,如:1</param>
        /// <returns>返回示例:select id,name from person where age>10 order by id limit 0,10 </returns>
        public override string GetSqlForPageSize(string selectSql, string strOrder, int PageSize, int PageIndex)
        {
            string sql = string.Format("{0} {1} limit {2},{3}", selectSql, strOrder, (PageIndex - 1) * PageSize, PageSize);
            return sql;
        }
        #endregion

        #region Is系列: 判断表/视图/列/存储过程是否存在
        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="schemaName">模式名,不起作用(使用数据库名)</param>
        /// <returns></returns>
        public override bool IsProcedureExist(string procName, string schemaName = null)
        {
            procName = DealSqlInject(procName);
            var objName = ParseObjectName(procName);

            string sql = $"show PROCEDURE status where Db ='{DataBase}' and name='{objName.Name}' and `type` = 'PROCEDURE'";
            var dt = SelectDataTable(sql);
            return dt.Rows.Count > 0;
        }

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="schemaName">模式名,不起作用(使用数据库名)</param>
        /// <returns></returns>
        public override bool IsTriggerExist(string triggerName, string schemaName = null)
        {
            triggerName = DealSqlInject(triggerName);
            var objName = ParseObjectName(triggerName);

            var sql = $"select count(1) from information_schema.TRIGGERS t where t.TRIGGER_SCHEMA = '{DataBase}' and TRIGGER_NAME='{objName.Name}'";
            return SelectScalar<int>(sql) > 0;
        }
        #endregion

        #region 基于数据库的Id和流水号生成器
        /// <summary>
        /// 初始化基础数据库缓存的Id和流水号生成环境
        /// </summary>
        /// <returns></returns>
        protected override bool InitDBCacheGenerator()
        {
            if (!IsTableExist(Settings.DBCacheGeneratorLogTableName))
            {
                #region 新建表 Settings.DBCacheGeneratorLogTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorLogTableName}(
  id int auto_increment primary key,-- 错误序号
  err_proc  varchar(200),-- 出现错误的存储过程或 触发器的名称
  lock_str  varchar(200),-- 申请的锁资源锁
  err_num   int,-- 错误号
  err_severity  int,-- 严重性
  err_state int,-- 错误状态号  
  err_line  int,-- 导致错误的例程中的行号
  err_msg   varchar(200),-- 错误消息的完整文本
  err_time  datetime -- 错误发生时间
)");
                #endregion
            }
            if (!IsTableExist(Settings.DBCacheGeneratorIdTableName))
            {
                #region 新建表 Settings.DBCacheGeneratorIdTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorIdTableName}(
	 tablename varchar(50), -- 引用表名
	 colname varchar(50), -- 引用列名
	 currentid bigint,
	 primary key(tablename,colname)
)");
                #endregion
            }
            if (!IsProcedureExist(Settings.DBCacheGeneratorIdProcedureName))
            {
                var procName = Settings.DBCacheGeneratorIdProcedureName;
                var tableName = Settings.DBCacheGeneratorIdTableName;
                var errTableName = Settings.DBCacheGeneratorLogTableName;
                #region 新建存储过程 Settings.DBCacheGeneratorIdProcedureName
                ExecuteSql($@"
-- Version=2021-09-19
/*
正常情况:
	1. 无缓存,实际表里也没匹配到
	2. 无缓存,实际表里匹配到
	3. 有缓存
异常情况:
	1. 实际表不存在等,造成查询报错
	2. 获取锁超时
*/

/* 调用实例
call __proc_generateid('test','id',1)
*/
CREATE PROCEDURE `{procName}`(in tablename2 varchar(200),in colname2 varchar(200),in count2 int)
begin	
	-- 当前id
    DECLARE   current2 bigint;
    DECLARE   sql2 nvarchar(1000);
    DECLARE   lockstr nvarchar(200);
    DECLARE   result int;
    DECLARE   current_database varchar(200);    
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN        
		GET CURRENT DIAGNOSTICS CONDITION 1
		 @errno = MYSQL_ERRNO,@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        set current_database =(select database());
	    set lockstr=concat(current_database,'_',tablename2,'_',colname2);
        set result = RELEASE_LOCK(lockstr);
        rollback;
        insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				    values('{procName}',lockstr,@errno,null,null, null ,@p2,current_timestamp());
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
	END;
    
    start TRANSACTION;
	-- 申请锁,20秒
    set current_database =(select database());
	set lockstr=concat(current_database,'_',tablename2,'_',colname2);
	set result =GET_LOCK(lockstr,20);
	if(result = 1) then    
		begin 
			-- 先从id表里查找现有值
			set current2 = (select currentid from `{tableName}` where `tablename` = tablename2 and `colname` = colname2 limit 1);
			if (current2 is null) then
					-- 表里尚未缓存,从实际表里查找id列的最大值
					begin
						set sql2=concat('select max(',colname2,') into @current2 from ',tablename2);
						#预处理动态SQL  
                        set @sqlcounts = sql2; 
						prepare stmt from @sqlcounts;  
						execute stmt;  
						deallocate prepare stmt;  
                        set current2 = @current2; 
						#获取动态SQL语句返回值  
						if (current2 is null) then
							-- 实际表里也没有值
							begin
								set current2=0+count2;
								insert into `{tableName}`(`tablename`, `colname`, `currentid`) values(tablename2, colname2, current2);
								select current2;
							end;
						else
							-- 实际表里有值
							begin
								set current2= current2 + count2;
								insert into `{tableName}`(`tablename`, `colname`, `currentid`) values(tablename2, colname2, current2);
								select current2;
							end;
						end if;
					end;
			else
					-- 表里已经缓存
					begin
						set current2 = count2+current2;
						update `{tableName}` set `currentid` = current2 where `tablename` = tablename2 and `colname` = colname2;
						select current2;
					end;
			 end if;  
		end;
	else
		begin
			rollback;
            set @p2=concat('20秒内未获取到锁,申请锁返回:',result);
			insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				    values('{procName}',lockstr,result,null,null, null ,@p2,current_timestamp());
			SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
        end;
    end if;
	set result= RELEASE_LOCK(lockstr);
    commit; 	
end");
                #endregion
            }
            if (!IsTableExist(Settings.DBCacheGeneratorSNOTableName))
            {
                #region 新建表(流水号生成) Settings.DBCacheGeneratorSNOTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorSNOTableName}
(
	tablename varchar(50), -- 引用表名
	colname varchar(50), -- 引用列名
	statictext varchar(200),
	machineidstr varchar(50),
	nowstr varchar(100),
	currentno bigint,
	primary key(tablename,colname,statictext)
)");
                #endregion
            }
            if (!IsProcedureExist(Settings.DBCacheGeneratorSNOProcedureName))
            {
                var procName = Settings.DBCacheGeneratorSNOProcedureName;
                var tableName = Settings.DBCacheGeneratorSNOTableName;
                var errTableName = Settings.DBCacheGeneratorLogTableName;
                #region 新建存储过程(流水号生成) Settings.DBCacheGeneratorSNOProcedureName
                ExecuteSql($@"
-- Version=2021-09-19
/*
正常情况:
	1. 无缓存,实际表里也没匹配到
	2. 无缓存,实际表里匹配到
	3. 有缓存,但时间戳对不上
	4. 有缓存,时间戳也对的上
异常情况:
	1. 实际表不存在等,造成查询报错
	2. 获取锁超时
*/

/* 调用实例
call {Settings.DBCacheGeneratorSNOProcedureName}('test','sno','SNO',null,'2021-09-20 00:00:00','SNO20210920%',11,1)
*/
-- 其实, 除了自增的序列号其他部分已经在程序中计算完成,这个要根据程序计算的结果求出对应的序列号
create procedure `{Settings.DBCacheGeneratorSNOProcedureName}`(
	in tablename2 varchar(50),-- 实际表名(关键值)
    in colname2 varchar(50),-- 实际列名(关键值)
    in statictext2 varchar(200), -- 静态文本,即: 除了时间戳和序列号的其他部分(包含机器id标识) (关键值)
    in machineidstr2 varchar(50),-- 机器id标识,仅用作记录
    in nowstr2 varchar(200),-- 当前流水号对应的时间戳字符串(格式为: yyyy-MM-dd HH:mm:ss)
    in likestr varchar(200),-- 程序中计算的除了自增序列号的其他部分,如: 'SNO20210919%', 用于无缓存时从真实表中匹配(关键值)
    in startindex int,-- 用于从实际表中查出流水号中截取序列号部分, 注意:程序传入的startindex是以0起始,而数据库中是以1起始(关键值) 
    in count2 int -- 批量生成时的数量
)
begin
	DECLARE current2 int;
	DECLARE tempno nvarchar(200);
	DECLARE sql2 nvarchar(1000);
	DECLARE templen int;
	DECLARE lockstr nvarchar(200);
	DECLARE result int;
	DECLARE currentnowstr varchar(200);
	DECLARE err_message varchar(500);
    DECLARE err_serverity varchar(50);
    DECLARE err_state varchar(50);
    DECLARE current_database varchar(200); 
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN        
		GET CURRENT DIAGNOSTICS CONDITION 1
			@errno = MYSQL_ERRNO,@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        set current_database =(select database());
		set lockstr = concat(current_database,'_',tablename2,'_',colname2,'_',statictext2); 
        set result = RELEASE_LOCK(lockstr);
        rollback;
        insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				    values('__proc_generatesno',lockstr,@errno,null,null, null ,@p2,current_timestamp());
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
	END;
	start transaction;
	-- 申请锁,20秒
    set current_database =(select database());
	set lockstr=concat(current_database,'_',tablename2,'_',colname2,'_',statictext2);
	set result =GET_LOCK(lockstr,20);
	if(result = 1) then
		begin
			-- 获取到了锁
			-- 先从流水号表里查找
            set current2=(select currentno from {tableName} where tablename = tablename2 and colname = colname2 and statictext=statictext2 limit 1);
            set currentnowstr=(select nowstr from {tableName} where tablename = tablename2 and colname = colname2 and statictext=statictext2 limit 1);
			if (current2 is null) then
				-- 表里尚未缓存,从实际表里查找流水号列的最大值
				begin
					set sql2=concat('select ',colname2,' into @tempno from ',tablename2,' where ',colname2, ' like ''',likestr,''' order by ',colname2,' desc limit 1');
					#预处理动态SQL  
					set @sqlcounts = sql2; 
					prepare stmt from @sqlcounts;  
					execute stmt;  
					deallocate prepare stmt;  
					set tempno = @tempno;
					#获取动态SQL语句返回值  
					if(tempno is null) then
						-- 实际表中也找不到参照
						begin
							insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(tablename2, colname2,statictext2,machineidstr2,nowstr2, count2);
							select count2;
						end;
					else
						-- 实际表中找到参照
						begin
							set current2=CONVERT(substring(tempno,startindex+1),SIGNED);
							set current2=current2+count2;
							insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(tablename2, colname2,statictext2,machineidstr2,nowstr2, current2);
							select current2;
						end;
					end if;
				end;
			else
				-- 缓存表里有值
				begin
					-- 判断时间戳是否匹配
					if (currentnowstr=nowstr2) then
						begin
							-- 时间戳匹配
							set current2 = current2+count2;
							update {tableName} set currentno = current2 ,machineidstr=machineidstr2 where tablename = tablename2 and colname = colname2 and statictext=statictext2;
							select current2;
						end;
					else
						begin
							-- 时间戳不匹配,从新开始
							set current2=count2;
							update {tableName} set currentno = current2,nowstr=nowstr2,machineidstr=machineidstr2 where tablename = tablename2 and colname = colname2 and statictext=statictext2;
							select current2	;
						end;
					end if;
				end;
			end if;
			commit;
		end;
	else
		begin
			-- 未获取到锁
			rollback;
			set @p2=concat('20秒内未获取到锁,申请锁返回:',result);
			insert INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
					values('__proc_generatesno',lockstr,result,null,null, null ,@p2,current_timestamp());
			SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @p2;
		end;
	end if;
    set current_database =(select database());
	set lockstr = concat(current_database,'_',tablename2,'_',colname2,'_',statictext2); 
	set result= RELEASE_LOCK(lockstr);
	commit; 
end");
                #endregion
            }
            return true;
        }

        /// <summary>
        /// 根据指定的表名和列名生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <returns></returns>
        protected override long InternalNewId(string tableName, string colName)
        {
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            EnsureInitGenerator();
            var id = SelectScalar<long>($"call {Settings.DBCacheGeneratorIdProcedureName} ('{DealSqlInject(tableName)}','{DealSqlInject(colName)}',1)");
            return id;
        }

        /// <summary>
        /// 根据指定的表名和列名批量生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <returns></returns>
        protected override List<long> InternalNewIds(string tableName, string colName, int count)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            EnsureInitGenerator();
            var id = SelectScalar<long>($"call {Settings.DBCacheGeneratorIdProcedureName} ('{DealSqlInject(tableName)}','{DealSqlInject(colName)}',{count})");
            var res = new List<long>();
            for (long i = id - count + 1; i <= id; i++)
            {
                res.Add(i);
            }
            return res;
        }

        /// <summary>
        /// 根据表名和列名生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <returns></returns>
        protected override string InternalNewSNO(string tableName, string colName, SerialFormat format)
        {
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            var now = DateTime.Now;
            var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(format, now, DotNetCommon.Machine.MachineIdString);
            var sql = $@" 
call {Settings.DBCacheGeneratorSNOProcedureName}(
    '{DealSqlInject(tableName)}',
    '{DealSqlInject(colName)}',
    '{statictext}',
    '{machineIdString}',
    '{snoNow.ToString(format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}',
    '{likestr}',
    {startindex},
1)";
            long no = SelectScalar<long>(sql);

            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var sno = likestr.Substring(0, startindex);
            var s = no.ToString();
            if (s.Length > chunk.Length)
            {
                sno += s;
            }
            else
            {
                sno += s.PadLeft(chunk.Length, '0');
            }
            return sno;
        }

        /// <summary>
        /// 根据表名和列名批量生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <returns></returns>
        protected override List<string> InternalNewSNOs(string tableName, string colName, SerialFormat format, int count)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            var now = DateTime.Now;
            var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(format, now, DotNetCommon.Machine.MachineIdString);
            var sql = $@" 
call {Settings.DBCacheGeneratorSNOProcedureName}(
    '{DealSqlInject(tableName)}',
    '{DealSqlInject(colName)}',
    '{statictext}',
    '{machineIdString}',
    '{snoNow.ToString(format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}',
    '{likestr}',
    {startindex},
{count})";

            long no = long.Parse(SelectScalar<string>(sql));
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var res = new List<string>();
            for (long i = no - count + 1; i <= no; i++)
            {
                var sno = likestr.Substring(0, startindex);
                var s = i.ToString();
                if (s.Length > chunk.Length)
                {
                    sno += s;
                }
                else
                {
                    sno += s.PadLeft(chunk.Length, '0');
                }
                res.Add(sno);
            }
            return res;
        }
        #endregion

        #region 数据库管理对象 Manage
        private DBManage _dbmanage = null;
        /// <summary>
        /// 数据库管理对象
        /// </summary>
        public override DBManage Manage => _dbmanage != null ? _dbmanage : _dbmanage = new MySqlManage(this);
        #endregion

        #region 运算符
        /// <summary>
        /// 数据库日期时间运算符
        /// </summary>
        protected override DateTimeSqlSegment GetDateTimeSqlSegment() => new MySqlDateTimeSqlSegment(this);
        protected override StringSqlSegment GetStringSqlSegment() => new MySqlStringSqlSegment(this);
        #endregion

        #region 使用SqlBulkCopy批量插入数据
        /// <summary>
        /// 使用SqlBulkCopy批量插入数据
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="tableName">目的表名,如果为空,则去dt.TableName</param>
        public void BulkCopy(DataTable dt, string tableName = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) tableName = dt.TableName;
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("必须指定要目的表名!");
            MySqlBulkCopy sbc = null;
            if (IsTran)
            {
                sbc = new MySqlBulkCopy((MySqlConnection)Connection, (MySqlTransaction)Transaction);
            }
            else
            {
                sbc = new MySqlBulkCopy((MySqlConnection)Connection);
            }
            RunInConnection(() =>
            {
                sbc.BulkCopyTimeout = 60 * 30;
                sbc.DestinationTableName = tableName;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sbc.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, dt.Columns[i].ColumnName));
                }
                sbc.WriteToServer(dt);
            });
        }

        #endregion

        #region 获取刚插入的自增id的Sql语句
        /// <summary>
        /// 获取刚插入的自增id的Sql语句
        /// </summary>
        /// <param name="sequenceName">序列名称</param>
        /// <returns></returns>
        public override string GetInsertedId(string sequenceName = null)
        {
            return $"select LAST_INSERT_ID();";
        }
        #endregion

        #region 基于数据库的分布式锁 RunInLock
        private static ConcurrentDictionary<string, SemaphoreSlim> _lockSemaphores = new ConcurrentDictionary<string, SemaphoreSlim>();
        protected override async Task<T> RunInLockInternalAsync<T>(string lock_str, Func<Task<T>> func, int getLockTimeoutSecond)
        {
            //先单机拦截并发
            var now = DateTime.Now;
            var asyncLock = _lockSemaphores.GetOrAdd(lock_str, _ => new SemaphoreSlim(1, 1));
            var b = await asyncLock.WaitAsync(getLockTimeoutSecond * 1000);
            if (!b) throw new Exception($"获取锁[{lock_str}]超时,单机内超时.");
            try
            {
                //单机通过后,再数据库锁内拦截
                var now2 = DateTime.Now;
                var timeout2 = getLockTimeoutSecond - (int)(now2 - now).TotalSeconds;
                if (timeout2 < 0) timeout2 = 0;
                lock_str = "dbutil:runinlock:" + lock_str;
                lock_str = DealSqlInject(lock_str);
                var sql = $"select GET_LOCK('{lock_str}',{timeout2})";
                return await RunInConnectionAsync(async () =>
                {
                    var res = await SelectScalarAsync<int>(sql);
                    if (res == 1)
                    {
                        //获取到锁
                        T result = default;
                        try
                        {
                            result = await func();
                        }
                        finally
                        {
                            try
                            {
                                await ExecuteSqlAsync($@"select RELEASE_LOCK('{lock_str}')");
                            }
                            catch (Exception ex)
                            {
                                logger.LogError($"尝试释放基于数据库的分布式锁失败(lock_str={lock_str}),异常信息:{ex?.Message}");
                            }
                        }
                        return result;
                    }
                    else
                    {
                        throw new Exception($"数据库获取锁失败: GET_LOCK 返回:{res}");
                    }
                });
            }
            finally
            {
                try { asyncLock.Release(); } catch { }
            }
        }
        #endregion

        #region 序列
        /// <summary>
        /// 是否支持序列
        /// </summary>
        /// <returns></returns>
        public override bool IsSupportSequence() => false;

        /// <summary>
        /// 获取 下一个序列值 的sql语句
        /// </summary>
        /// <param name="name">序列名称</param>
        /// <returns></returns>
        public override string NextSequenceValue(string name) => throw new NotSupportedException();
        #endregion
    }
}
